package bank.passbook.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;

import bank.datasource.DatabaseUtil;
import bank.passbook.vo.Passbook;

public class PassbookDAO {
	private static PassbookDAO dao = new PassbookDAO();
	private DatabaseUtil dbUtil = DatabaseUtil.getInstance();
	private PassbookDAO() {	}
	public static PassbookDAO getInstance(){
		return dao;
	};
	
	public int	updatePassbookById(String accountNumber, int money) throws Exception{
		System.out.println(0);
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "update Passbook set money = money + ? where account_number = ?";
		int count = 0;
		try {
			conn = dbUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, money);
			pstmt.setString(2, accountNumber);
			count = pstmt.executeUpdate();
		} finally {
			dbUtil.close(conn, pstmt);
		}
		return count;
	}
	
	public Passbook selectPassbookByAccountNo(String accountNumber) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		String sql = "select account_number, owner, money, createDate, interest from passbook where account_number = ?";
		ResultSet rs = null;
		Passbook passbook = null;
		
		try {
			conn = dbUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, accountNumber);	
			rs = pstmt.executeQuery();
			if(rs.next()){
				passbook = new Passbook(rs.getString(1), rs.getString(2), rs.getInt(3), rs.getString(4), rs.getDouble(5));
			}
		} finally {
			dbUtil.close(conn, pstmt, rs);
		}
		return passbook;
	}
	
	public ArrayList<Passbook> selectPassbookList() throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "select account_number, owner, money, createDate, interest from passbook";
		ResultSet rs = null;
		
		ArrayList<Passbook> list = new ArrayList<Passbook>();
		
		try {
			conn = dbUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				list.add(new Passbook(rs.getString(1), rs.getString(2), rs.getInt(3), rs.getString(4), rs.getDouble(5)));
			}
		} finally {
			dbUtil.close(conn, pstmt, rs);
		}
		return list;
	}
	
	public int updatePassbookByInterest() throws SQLException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "update passbook set money = round(money * (interest + 1))";
		int count = 0;
		
		try {
			conn = dbUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			count = pstmt.executeUpdate();
		} finally {
			dbUtil.close(conn, pstmt);
		}
		return count;
	}
}